﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DAL;

namespace BLL
{
   public class ClsDMCongTrinhBus
    {
        public ClsDMCongTrinhInfo Info = new ClsDMCongTrinhInfo();
        public DataTable SelectTinh()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @"SELECT DISTINCT MaCT,TenCT   
                                FROM DMCongTrinh INNER JOIN TTCHUNG ON DMCongTrinh.MaCT=TTCHUNG.DeAnBC
                                INNER JOIN TinhTP ON TTCHUNG.Tinh=TinhTP.MaTinh
                                where TinhTP.MaTinh like'%" + Info.MaTinh + "%'";
            if (oConnection.ExecuteSelect(strSql))return oConnection.ODataTable;
            else return null;
        }
        public DataTable SelectAll()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @"SELECT * FROM DMCongTrinh";

            if (oConnection.ExecuteSelect(strSql))
            {
                oConnection.ODataTable.Columns.Add("STT");
                int i = 0;
                foreach (DataRow oDRow in oConnection.ODataTable.Rows)
                {
                    oDRow["STT"] = (i + 1);
                    i++;
                }
                return oConnection.ODataTable;
            }
            else return null;
        }
        public bool Them()
        {
            string strSql = "EXECUTE InsertDMCongTrinh '" + Info.MaCT + "', N'" + Info.TenCT + "', N'" + Info.DVCapGiayPhep + "', N'" + Info.DVThiCong + "', N'" + Info.MucDichSD + "', N'" + Info.DTCapNuoc + "', '" +Info.NamXDVanHanh+ "'";
            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));
        }

        public bool Sua()
        {
            string strSql = "EXECUTE UpdateDMCongTrinh'" + Info.MaCT + "', N'" + Info.TenCT + "', N'" + Info.DVCapGiayPhep + "', N'"  + Info.DVThiCong + "', N'" + Info.MucDichSD + "', N'" + Info.DTCapNuoc + "', '" + Info.NamXDVanHanh + "'";
            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));
        }

        public bool Xoa()
        {
            string strSql = "EXECUTE DeleteDMCongTrinh'" + Info.MaCT + "'";
            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));

        }
    }
}
